package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Product;
import ModelLayer.Supplier;

public class DbProduct implements IfDbProduct {
	private Connection con;

	public DbProduct() {
		con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Product> getAllProducts(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	private ArrayList<Product> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();

		String query = buildQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Product productObject = new Product();
				productObject = buildProduct(results);
				list.add(productObject);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Product singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Product productObject = new Product();

		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				productObject = buildProduct(results);
				stmt.close();
			} else {
				productObject = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return productObject;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT productNr, name, purchasePrice, salesPrice, rentPrice, minStock, inStock, size, colour, type, manufacturer, material, countryOfOrigin, description, supplierid FROM product";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	private Product buildProduct(ResultSet results) {
		Product productObject = new Product();
		try {
			productObject.setProductNr(results.getInt("productNr"));
			productObject.setName(results.getString("name"));
			productObject.setPurchasePrice(results.getDouble("purchasePrice"));
			productObject.setSalesPrice(results.getDouble("salesPrice"));
			productObject.setRentPrice(results.getDouble("rentPrice"));
			productObject.setMinStock(results.getInt("minStock"));
			productObject.setInStock(results.getInt("inStock"));
			productObject.setSize(results.getInt("size"));
			productObject.setColour(results.getString("colour"));
			productObject.setType(results.getString("type"));
			productObject.setManufacturer(results.getString("manufacturer"));
			productObject.setMaterial(results.getString("material"));
			productObject.setCountryOfOrigin(results.getString("countryOfOrigin"));
			productObject.setDescription(results.getString("description"));
			productObject.setSupplier(new Supplier(results.getInt("supplierid")));
		} catch (Exception e) {
			System.out.println("There is a failure in building the Product");
		}
		return productObject;
	}

	public Product findProductByName(int productNr, boolean retriveAssociation) {
		String wClause = "productNr = '" + productNr + "'";
		System.out.println("SearchProduct " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	public int insertProduct(Product product) throws Exception {
		// call to get the next ID number
		int nextID = GetMax.getMaxId("Select max(ProductID) from Product");
		nextID = nextID + 1;
		System.out.println("next ID = " + nextID);

		int lol = -1;
		String query = "INSERT INTO product(productid, productNr, name, purchasePrice, salesPrice, rentPrice, minStock, inStock, size, colour, type, manufacturer, material, countryOfOrigin, description, supplierid) VALUES('"
				+ nextID
				+ "','"
				+ product.getProductNr()
				+ "','"
				+ product.getName()
				+ "','"
				+ product.getPurchasePrice()
				+ "','"
				+ product.getSalesPrice()
				+ "','"
				+ product.getRentPrice()
				+ "','"
				+ product.getMinStock()
				+ "','"
				+ product.getInStock()
				+ "','"
				+ product.getSize()
				+ "','"
				+ product.getColour()
				+ "','"
				+ product.getType()
				+ "','"
				+ product.getManufacturer()
				+ "','"
				+ product.getMaterial()
				+ "','"
				+ product.getCountryOfOrigin()
				+ "','"
				+ product.getDescription()
				+ "','"
				+ product.getSupplier().getSupplierID() + "')";
		System.out.println("hertil " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			lol = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Product is not created");
			throw new Exception("Product is not inserted correct");
		}
		return (lol);
	}

	public int updateProduct(Product product) {
		Product productObject = product;
		int lol = -1;

		String query = "UPDATE product SET " + "productNr = '"
				+ productObject.getProductNr() + "', " + "name = '"
				+ productObject.getName() + "', " + "purchasePrice = '"
				+ productObject.getPurchasePrice() + "', " + "salesPrice = '"
				+ productObject.getSalesPrice() + "', " + "rentPrice = '"
				+ productObject.getRentPrice() + "', " + "minStock = '"
				+ productObject.getMinStock() + "', " + "inStock = '"
				+ productObject.getInStock() + "', " + "size = '"
				+ productObject.getSize() + "', " + "colour = '"
				+ productObject.getColour() + "', " + "type = '"
				+ productObject.getType() + "', " + "manufacturer = '"
				+ productObject.getManufacturer() + "', " + "material = '"
				+ productObject.getMaterial() + "', " + "countryOfOrigin = '"
				+ productObject.getCountryOfOrigin() + "', " + "description = '"
				+ productObject.getDescription() + "', " + "supplierid = '"
				+ productObject.getSupplier() + "'"	+ 
				"WHERE productNr = '" + productObject.getProductNr() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			lol = stmt.executeUpdate(query);

			stmt.close();
		} catch (Exception ex) {
			System.out.println("Update exception in Product db: " + ex);
		}
		return (lol);
	}

	public int deleteProduct(int productNr) {
		int lol = -1;

		String query = "DELETE FROM product WHERE productNr = '" + productNr
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			lol = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in Product db: " + ex);
		}
		return (lol);
	}

}
